package com.scalar.chiptrack.adaptor.chartered;

import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.adaptor.AdaptorConstants;
import com.scalar.chiptrack.adaptor.FabSortLot;
import com.scalar.chiptrack.adaptor.wipfed.WipFedStatus;
import com.scalar.chiptrack.utils.StringUtils;
import com.scalar.chiptrack.utils.Logger;
import com.scalar.ScalarException;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.Iterator;
import java.util.HashMap;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.ResultSet;

/**  Used To Save the All the WIP Status and Shipment Details Daily in the Tables
 * Created by IntelliJ IDEA.
 * User: ChandraSekhar
 * Date: Sep 1, 2006
 * Time: 9:33:04 PM
 */
public class CharteredDAO implements AdaptorConstants
{
    private static Logger s_logger = null;
    /**
    * @param dbTransaction
    * @param wipList
    * @throws com.scalar.ScalarException
    */
   public static void saveCharteredWipDetails( DBTransaction dbTransaction, ArrayList wipList ) throws ScalarException
   {
      Connection conn         = null;
      PreparedStatement pstmt = null;
      boolean success         = false;

      CharteredWipStatus wipStatus = null;

      try
      {
          // Get the connection from regular datasource if its not in a manual transaction
          if( dbTransaction == null )
          {
              conn = DataSource.getConnection(2);
          }
          else
          {
              conn = dbTransaction.getConnection(2);
          }

          Calendar cal = Calendar.getInstance();
          java.util.Date todayDate = cal.getTime();
          String currentDate = StringUtils.formatDateToString( todayDate, "MM/dd/yyyy" );
          // Step 1
          // Delete all THE PREVIOUS DATA IN TABLE
          pstmt = conn.prepareStatement("DELETE FROM tblLotStatus WHERE ImportDate = ?");
          pstmt.setString(1, currentDate);
          pstmt.executeUpdate();
          pstmt.close();
          pstmt = null;

          // Delete all THE PREVIOUS DATA IN HISTORY TABLE
          /*pstmt = conn.prepareStatement("DELETE FROM Chartered_Wip_Status_History_Table WHERE ADAPTOR_DATE = ?");
          pstmt.setString(1,currentDate);
          pstmt.executeUpdate();
          pstmt.close();
          pstmt = null;*/
          //pstmt = conn.prepareStatement("{ call sp_save_chartered_wip_status ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) }");
          pstmt = conn.prepareStatement( "INSERT INTO tblLotStatus( [Customer Name],[Fab Group],[Foundry Part Name],[Customer Part Name],[Geometry Name],[Process Name],[PO#],[SO#],[Lot Id],[Lot Type],[Lot Priority]," +
                  "[Location],[Lot State],[Stage Name],[Curr Stage #],[Curr Step #],[Balance Mask #],[Lot Wfr Qty],[Wafer ID],[Actual Start Date],[Act Fabout Date],[CRD],[OSD],[CSD],[EDO],[ImportDate])\t\t\n" +
                  "VALUES( 'Mobius Microsystems', ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" );

          Iterator wipStatusIterator = wipList.iterator();
          while( wipStatusIterator.hasNext() )
          {
              wipStatus = ( CharteredWipStatus ) wipStatusIterator.next();

              if (wipStatus.getCustomerProduct() != null)
              {
                  pstmt.setString( 1,  wipStatus.getFabGroup() );
                  pstmt.setString( 2,  wipStatus.getVendorPartNumber() );
                  pstmt.setString( 3,  wipStatus.getCustomerProduct() );
                  pstmt.setString( 4,  wipStatus.getGeometryname() );
                  pstmt.setString( 5,  wipStatus.getProcessName() );
                  pstmt.setString( 6,  wipStatus.getCustPo() );
                  pstmt.setString( 7,  wipStatus.getSoNo() );
                  pstmt.setString( 8,  wipStatus.getCustomerLot() );
                  pstmt.setString( 9,  wipStatus.getLotType() );
                  pstmt.setString( 10, wipStatus.getLotPriority() );
                  pstmt.setString( 11, wipStatus.getLocation() );
                  pstmt.setString( 12, wipStatus.getLotState() );
                  pstmt.setString( 13, wipStatus.getCurrentStage() );
                  pstmt.setString( 14, wipStatus.getStagePosition() );
                  pstmt.setString( 15, wipStatus.getCurrStepNo() );
                  pstmt.setString( 16, wipStatus.getBalanceMaskNo() );
                  pstmt.setString( 17, wipStatus.getWaferQty() );
                  pstmt.setString( 18,  wipStatus.getWaferID() );
                  pstmt.setString( 19, wipStatus.getDateIn() );
                  pstmt.setString( 20, wipStatus.getActFaboutDate() );
                  pstmt.setString( 21, wipStatus.getExpectedDateOut() );
                  pstmt.setString( 22, wipStatus.getOsdDate() );
                  pstmt.setString( 23, wipStatus.getCommitDate() );
                  pstmt.setString( 24, wipStatus.getEdoDate() );
                  pstmt.setString( 25, currentDate);

                  pstmt.addBatch();
              }
          }

          pstmt.executeBatch();
          success = true;
      }

      catch( SQLException sqle )
      {
          sqle.printStackTrace();
          throw new ScalarException( "Could not save Chartered Wip Information into database", sqle );
      }

      finally
      {

          // Commit/Rollback here it selef -- if its not in a manual transaction
          if( dbTransaction == null )
          {
              try
              {
                  if( conn != null )
                  {
                      if( success == true )
                      {
                          conn.commit();
                      }
                      else
                      {
                          conn.rollback();
                      }
                  }
              }
              catch( SQLException ignore )
              {
              }
          }

          if( pstmt != null )
          {
              try
              {
                  pstmt.close();
              }
              catch( SQLException ignore )
              {
              }
          }
          if( conn != null )
          {
              try
              {
                  conn.close();
              }
              catch( SQLException ignore )
              {
              }
          }
      }
    }

    /**
     *
     * @param dbTransaction
     * @param shipList
     * @throws ScalarException
     */
    public static void saveCharteredShipDetails(DBTransaction dbTransaction, ArrayList shipList) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        boolean success         = false;


        CharteredShipStatus shipStatus = null;

        try {
            // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection(2);
            }
            else
            {
                conn = dbTransaction.getConnection(2);
            }

            /*Calendar cal = Calendar.getInstance();
            java.util.Date todayDate = cal.getTime();
            String currentDate = StringUtils.formatDateToString(todayDate, "MM/dd/yyyy");*/

            // Delete all THE PREVIOUS DATA IN TABLE
            /*pstmt = conn.prepareStatement("DELETE FROM Chartered_Ship_Status_Table");
            pstmt.executeUpdate();
            pstmt.close();
            pstmt = null;*/

            // Delete all THE PREVIOUS DATA IN HISTORY TABLE
            /*pstmt = conn.prepareStatement("DELETE FROM Chartered_Ship_Status_History_Table WHERE ADAPTOR_DATE = ?");
            pstmt.setString(1, currentDate);
            pstmt.executeUpdate();
            pstmt.close();
            pstmt = null;*/

            //pstmt = conn.prepareStatement("{ call sp_save_chartered_ship_info ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) }");
            pstmt = conn.prepareStatement("INSERT INTO tblShipAlert ( [Customer Name],[Ship Date],[Invoice #],[Invoice Date],[PO#],[SO#],[Process Name],[Customer Partname],[Foundry Part Name],[Lot Id],[Lot Type],[Lot Priority],[Calculated GDPW],[Calculated GDPL],[Cycle Time],[Bill Qty (Wfr)],[Wfr Ids],[v Bill Die]," +
                    "[Ship To Location],[Bill To Location],[ETA],[ETD],[Forwarder],[HAWB],[MAWB],[Flight #],[Connecting Flight #] )" +
                    "VALUES ('Mobius Microsystems', ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ");


            Iterator shipIterator = shipList.iterator();
            while ( shipIterator.hasNext() )
            {
                shipStatus = ( CharteredShipStatus ) shipIterator.next();

                //log( shipStatus + "\n" );
                if (shipStatus.getCustomerLot() == null ) continue;
                pstmt.setString(1, shipStatus.getShipDate());
                pstmt.setString(2, shipStatus.getInvoiceNo() );
                pstmt.setString(3, shipStatus.getInvoiceDate() );
                pstmt.setString(4, shipStatus.getCustPo() );
                pstmt.setString(5, shipStatus.getSoNO());
                pstmt.setString(6, shipStatus.getProcessName());
                pstmt.setString(7, shipStatus.getCustPartNo() );
                pstmt.setString(8, shipStatus.getVendorPartNo() );
                pstmt.setString(9, shipStatus.getCustomerLot() );
                pstmt.setString(10, shipStatus.getLotType() );
                pstmt.setString(11, shipStatus.getLotPriority() );
                pstmt.setString(12, shipStatus.getGrossDPW() );
                pstmt.setString(13, shipStatus.getGorssDPL() );
                pstmt.setString(14, shipStatus.getCycleTime() );
                pstmt.setString(15, shipStatus.getShipWaferQty() );
                pstmt.setString(16, shipStatus.getWaferID());
                pstmt.setString(17, shipStatus.getShipDieQty() );
                pstmt.setString(18, shipStatus.getShipToLocation() );
                pstmt.setString(19, shipStatus.getBilToLocation() );
                pstmt.setString(20, shipStatus.getEtaDate());
                pstmt.setString(21, shipStatus.getEtdDate());
                pstmt.setString(22, shipStatus.getFarworder() );
                pstmt.setString(23, shipStatus.getHawbNo() );
                pstmt.setString(24, shipStatus.getMawbNo());
                pstmt.setString(25, shipStatus.getFlightNo() );
                pstmt.setString(26, shipStatus.getConnFlightNo() );
                //pstmt.setString(27, currentDate);

                pstmt.addBatch();
            }

            pstmt.executeBatch();
            success = true;
        }

        catch (SQLException sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException("Could not save Chartered Ship Information into database", sqle);
        }

        finally {

            // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbTransaction == null) {
                try {
                    if (conn != null) {
                        if (success == true) {
                            conn.commit();
                        } else {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {
                }
            }

            if (pstmt != null) {
                try {
                    pstmt.close();
                }
                catch (SQLException ignore) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                }
                catch (SQLException ignore) {
                }
            }
        }
    }

    /**
     *
     * @param dbTransaction
     * @param wipList
     * @throws ScalarException
     */
    public static void saveWipFedCharteredDetails(DBTransaction dbTransaction, ArrayList wipList) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        boolean success         = false;
        WipFedStatus  wipStatus = null;

        try {
            // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            Calendar cal = Calendar.getInstance();
            java.util.Date todayDate = cal.getTime();
            String currentDate = StringUtils.formatDateToString(todayDate, "MM/dd/yyyy");
            // Step 1
            // Delete all THE PREVIOUS DATA IN TABLE
            pstmt = conn.prepareStatement("DELETE FROM Wip_Fed_Chartered_Table");
            pstmt.executeUpdate();
            pstmt.close();
            pstmt = null;

            // Delete all THE PREVIOUS DATA IN HISTORY TABLE
            pstmt = conn.prepareStatement("DELETE FROM Wip_Fed_Chartered_History_Table WHERE FILE_DATE = ?");
            pstmt.setString(1, currentDate);
            pstmt.executeUpdate();
            pstmt.close();
            pstmt = null;

            pstmt = conn.prepareStatement("{ call sp_save_wip_Fed_chartered_status ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) }");

            Iterator wipStatusIterator = wipList.iterator();
            while (wipStatusIterator.hasNext())
            {
                wipStatus = (WipFedStatus) wipStatusIterator.next();

                log(wipStatus.toString());

                if (wipStatus.getCharteredPartNo() != null )
                {
                    pstmt.setString(1, wipStatus.getLotNumber());
                    pstmt.setString(2, wipStatus.getCharteredPartNo());
                    pstmt.setString(3, wipStatus.getStartDate());
                    pstmt.setString(4, wipStatus.getRsDate());
                    pstmt.setString(5, wipStatus.getOsDate());
                    pstmt.setString(6, wipStatus.getOutDate());
                    pstmt.setString(7, wipStatus.getMesQty());
                    pstmt.setString(8, wipStatus.getMesStep());
                    pstmt.setString(9, wipStatus.getPsType());
                    pstmt.setString(10, currentDate);
                    pstmt.addBatch();
                }
            }

            pstmt.executeBatch();
            success = true;
        }

        catch (SQLException sqle) {
            sqle.printStackTrace();
            throw new ScalarException("Could not save Chartered Wip Information into database", sqle);
        }

        finally {

            // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbTransaction == null) {
                try {
                    if (conn != null) {
                        if (success == true) {
                            conn.commit();
                        } else {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {
                }
            }

            if (pstmt != null) {
                try {
                    pstmt.close();
                }
                catch (SQLException ignore) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                }
                catch (SQLException ignore) {
                }
            }
        }
    }

    /**
     *
     * @return
     * @throws Exception
     */
    public static HashMap getCharteredFabList() throws Exception
    {
        HashMap charteredMap                    = new HashMap();
        Connection conn                         = null;
        PreparedStatement pstmt                 = null;
        ResultSet rset                          = null;

        String partNumber       = null;
        String lotNumber        = null;
        FabSortLot fabSortLot   = null;

        String actStartDate;
        String actFabOutDate;
        String crd;
        String osd;
        String csd;
        String edo;
        String waferQty;
        String stage;
        String stagePosition;
        String psType;
        String waferId;
        String customerSO;
        String customerPO;
        String lotType;

        try
        {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement(" SELECT CUSTOMERLOT, CUSTOMERPRODUCT, ACTSTARTDATE, ACTFABOUTDATE, CRD, OSD, CSD, " +
                    "EDO, WAFERQTY, CURRENTSTAGE, STAGEPOSITION, 'FAB' as PS_TYPE, WAFERID, CUSTPO, SONO, LOTTYPE FROM  CHARTERED_WIP_STATUS_TABLE " +
                    "UNION SELECT LOT_NUMBER, PART_NUMBER, START_DATE, RS_DATE, NULL, NULL, " +
                    "OS_DATE, OUT_DATE, QTY, MES_STEP, NULL, PS_TYPE, NULL, NULL, NULL, NULL FROM WIP_FED_CHARTERED_TABLE " );

            rset = pstmt.executeQuery();

            while ( rset.next() )
            {

                /*charteredWipStatus.setCustomerProduct( rset.getString( "CUSTOMERLOT" ) );
                charteredWipStatus.setCustomerLot( rset.getString( "CUSTOMERPRODUCT" ) );
                charteredWipStatus.setDateIn( rset.getString("ACTSTARTDATE") );
                charteredWipStatus.setActFaboutDate( rset.getString( "ACTFABOUTDATE" ));
                charteredWipStatus.setExpectedDateOut( rset.getString("CRD") );
                charteredWipStatus.setOsdDate( rset.getString("OSD") );
                charteredWipStatus.setCommitDate( rset.getString("CSD") );
                charteredWipStatus.setEdoDate( rset.getString( "EDO" ) );
                charteredWipStatus.setWaferQty( rset.getString( "WAFERQTY") );
                charteredWipStatus.setCurrentStage( rset.getString("CURRENTSTAGE") );
                charteredWipStatus.setStagePosition( rset.getString("STAGEPOSITION") );
                charteredWipStatus.setPsType( rset.getString("PS_TYPE") );*/



                partNumber      = rset.getString( "CUSTOMERPRODUCT" );
                lotNumber       = rset.getString( "CUSTOMERLOT" );
                waferQty        = rset.getString( "WAFERQTY" );
                actStartDate    = rset.getString( "ACTSTARTDATE" );
                actFabOutDate   = rset.getString( "ACTFABOUTDATE" );
                crd             = rset.getString( "CRD" );
                osd             = rset.getString( "OSD" );
                csd             = rset.getString( "CSD" );
                edo             = rset.getString( "EDO" );
                stage           = rset.getString( "CURRENTSTAGE" );
                stagePosition   = rset.getString( "STAGEPOSITION" );
                psType          = rset.getString( "PS_TYPE" );
                waferId         = rset.getString( "WAFERID" );
                customerPO      = rset.getString( "CUSTPO" );
                customerSO      = rset.getString( "SONO" );
                lotType         = rset.getString( "LOTTYPE" );

                if (lotNumber != null && partNumber != null)
                {
                    fabSortLot = (FabSortLot) charteredMap.get(partNumber + lotNumber);
                    if (fabSortLot == null)
                    {
                        fabSortLot = new FabSortLot();
                        charteredMap.put(partNumber + lotNumber, fabSortLot);
                    }
                    int quantity = 0;
                    if (fabSortLot.getQtyIn() != null)
                    {
                        try
                        {
                            quantity = Integer.parseInt(fabSortLot.getQtyIn());
                        }
                        catch (NumberFormatException ignore)
                        {
                        }
                        try
                        {
                            quantity += Integer.parseInt( waferQty );
                        }
                        catch (NumberFormatException ignore)
                        {
                        }
                    }
                    else
                    {
                        try
                        {
                            quantity = Integer.parseInt( waferQty );
                        }
                        catch (NumberFormatException ignore)
                        {
                        }
                    }

                    String dateIn = null;
                    String shipFcstDate = null;
                    String shipConfDate = null;
                    String expectedDateOut = null;
                    String outLookDate = null;

                    if ( actStartDate != null && actStartDate.trim().length() > 0)
                    {
                        dateIn = StringUtils.formatDate( rset.getString("ACTSTARTDATE"), "dd-MMM-yy", "MM/dd/yyyy");
                    }

                    if ( edo != null && edo.trim().length() > 0)
                    {
                        expectedDateOut = StringUtils.formatDate( edo, "dd-MMM-yy", "MM/dd/yyyy");
                    }

                    if (actFabOutDate != null && actFabOutDate.trim().length() > 0)
                    {
                        outLookDate = StringUtils.formatDate( actFabOutDate, "dd-MMM-yy", "MM/dd/yyyy");
                    }

                    if ( csd != null && csd.trim().length() > 0)
                    {
                        shipFcstDate = StringUtils.formatDate(csd, "dd-MMM-yy", "MM/dd/yyyy");
                    }
                    if (crd != null && crd.trim().length() > 0)
                    {
                        shipConfDate = StringUtils.formatDate(crd, "dd-MMM-yy", "MM/dd/yyyy");
                    }


                   /* fabSortLot.setFabId(wipStatusReport.getFabGroup());
                    fabSortLot.setFabPartName(wipStatusReport.getVendorPartNumber());
                    fabSortLot.setProcessGen(wipStatusReport.getGeometryname());
                    fabSortLot.setProcess(wipStatusReport.getProcessName());
                    fabSortLot.setWsQty(wipStatusReport.getWaferQty());
                    fabSortLot.setSoNumber(wipStatusReport.getSoNo());
                    fabSortLot.setPriorty(wipStatusReport.getLotPriority());
                    fabSortLot.setLayerPosition(wipStatusReport.getBalanceMaskNo());
                    fabSortLot.setShip_fcst_date(shipFcstDate);
                    fabSortLot.setShip_conf_date(shipConfDate);*/


                    fabSortLot.setLotId(lotNumber);
                    fabSortLot.setCustProduct(partNumber);
                    fabSortLot.setDateIn(dateIn);
                    fabSortLot.setQtyIn(quantity + "");
                    fabSortLot.setStage(stage);
                    fabSortLot.setStagePosition(stagePosition);
                    fabSortLot.setForcastedDateOut(expectedDateOut);
                    fabSortLot.setOutLookDate(outLookDate);
                    fabSortLot.setVendorName(VENDOR_NAME_CHARTERED);
                    fabSortLot.setWaferID(waferQty);
                    fabSortLot.setPsType(psType);
                    fabSortLot.setWaferID( waferId );
                    fabSortLot.setShip_fcst_date(shipFcstDate);
                    fabSortLot.setShip_conf_date(shipConfDate);
                    fabSortLot.setSoNumber( customerSO);
                    fabSortLot.setPoNumber( customerPO );

                    if( lotType == null ||"PO".equalsIgnoreCase(lotType) )
                    {
                        fabSortLot.setLotCategory(LOT_CATEGORY_TYPE_PROD);
                    }
                    else
                    {
                        fabSortLot.setLotCategory(LOT_CATEGORY_TYPE_ENGG);
                    }
                    if ( lotType != null && "HELD".equalsIgnoreCase(lotType))
                    {
                        fabSortLot.setLotStatus(LOT_STATUS_HOLD);
                    }
                    else
                    {
                        fabSortLot.setLotStatus(LOT_STATUS_RUNNING);
                    }
                }
            }
        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not load all Chartered Lots from the database", sqle );
        }
        finally
        {
            if ( rset != null )
            {
                try
                {
                    rset.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }

        return charteredMap;
    }
    /**
    *
    * @param message
    */
    private static void log (String message)
    {
        if ( message == null) return;

        if (s_logger != null)
        {
            synchronized (s_logger)
            {
                s_logger.logDebug (message + LINE_SEPARATOR);
            }
        }
        else
        {
            System.out.println (message);
        }
    }

    public static void setLogger (Logger logger)
    {
        s_logger = logger;
    }
}
